﻿using System.Data;
using System.Text;

using Tszy.Unlimited.Base.Collections;
using Tszy.Unlimited.Base.Common;
using Tszy.Unlimited.Base.Extensions;
using Tszy.Unlimited.Base.Objects.Property;

namespace Tszy.Unlimited.Data.AdoNet.Sql.Providers
{
    /// <summary>
    ///
    /// </summary>
    public static class DataProviderExtensions
    {
        #region AddAndGetKeyId

        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="TSource"></typeparam>
        /// <param name="dataProvider"></param>
        /// <param name="source"></param>
        /// <param name="tableName"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static object AddAndGetKeyId<TSource>(this IDataProvider dataProvider, TSource source, string tableName, IDbTransaction transaction)
        {
            //当传入的表名是空的时候，自动使用类名转换成小写字母做为表名。
            if (string.IsNullOrWhiteSpace(tableName))
            {
                tableName = source.GetType().Name.ToLower();
            }

            IDictionary<string, object> objectValues = FindProperties(source);
            var useFileds = objectValues.Keys;

            var sqlBuilder = new StringBuilder();

            if (dataProvider.DatabaseType == DatabaseType.SqlServer)
            {
                var columns = string.Join(",", "[" + useFileds + "]");
                var values = string.Join(",", useFileds.Select(p => "@" + p));

                sqlBuilder.AppendFormat("INSERT INTO {0}({1}) VALUES ({2});", tableName, columns, values);
                sqlBuilder.Append("SELECT @@IDENTITY");
            }

            if (dataProvider.DatabaseType == DatabaseType.MySql || dataProvider.DatabaseType == DatabaseType.MariaDb)
            {
                var columns = string.Join(",", useFileds.Select(p => $"`{p}`"));
                var values = string.Join(",", useFileds.Select(p => "@" + p));

                sqlBuilder.AppendFormat("INSERT INTO {0}({1}) VALUES ({2});", tableName, columns, values);
                sqlBuilder.Append("SELECT LAST_INSERT_ID()");
            }

            return dataProvider.Field<object>(sqlBuilder.ToString(), objectValues, transaction);
        }

        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="TSource"></typeparam>
        /// <param name="dataProvider"></param>
        /// <param name="source"></param>
        /// <param name="tableName"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static async Task<object> AddAndGetKeyIdAsync<TSource>(this IDataProvider dataProvider, TSource source, string tableName, IDbTransaction transaction)
        {
            //当传入的表名是空的时候，自动使用类名转换成小写字母做为表名。
            if (string.IsNullOrWhiteSpace(tableName))
            {
                tableName = source.GetType().Name.ToLower();
            }

            IDictionary<string, object> objectValues = FindProperties(source);
            var useFileds = objectValues.Keys;

            var sqlBuilder = new StringBuilder();

            if (dataProvider.DatabaseType == DatabaseType.SqlServer)
            {
                var columns = string.Join(",", "[" + useFileds + "]");
                var values = string.Join(",", useFileds.Select(p => "@" + p));

                sqlBuilder.AppendFormat("INSERT INTO {0}({1}) VALUES ({2});", tableName, columns, values);
                sqlBuilder.Append("SELECT @@IDENTITY");
            }

            if (dataProvider.DatabaseType == DatabaseType.MySql || dataProvider.DatabaseType == DatabaseType.MariaDb)
            {
                var columns = string.Join(",", useFileds.Select(p => $"`{p}`"));
                var values = string.Join(",", useFileds.Select(p => "@" + p));

                sqlBuilder.AppendFormat("INSERT INTO {0}({1}) VALUES ({2});", tableName, columns, values);
                sqlBuilder.Append("SELECT LAST_INSERT_ID()");
            }

            return await dataProvider.FieldAsync<object>(sqlBuilder.ToString(), objectValues, transaction);
        }

        #endregion AddAndGetKeyId

        #region Add

        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="TSource"></typeparam>
        /// <param name="dataProvider"></param>
        /// <param name="source"></param>
        /// <param name="tableName"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static bool Add<TSource>(this IDataProvider dataProvider, TSource source, string tableName, IDbTransaction transaction)
        {
            var sqlBuilder = new StringBuilder();

            //var fields = GetFields(source.GetType());

            //当传入的表名是空的时候，自动使用类名转换成小写字母做为表名。
            if (string.IsNullOrWhiteSpace(tableName))
            {
                tableName = source.GetType().Name.ToLower();
            }

            IDictionary<string, object> objectValues = FindProperties<TSource>(source);
            var useFileds = objectValues.Keys;

            if (dataProvider.DatabaseType == DatabaseType.SqlServer)
            {
                var columns = string.Join(",", "[" + useFileds + "]");
                var values = string.Join(",", useFileds.Select(p => "@" + p));

                sqlBuilder.AppendFormat("INSERT INTO {0}({1}) VALUES ({2});", tableName, columns, values);
            }

            if (dataProvider.DatabaseType == DatabaseType.MySql || dataProvider.DatabaseType == DatabaseType.MariaDb)
            {
                var columns = string.Join(",", useFileds.Select(p => $"`{p}`"));
                var values = string.Join(",", useFileds.Select(p => "@" + p));

                sqlBuilder.AppendFormat("INSERT INTO {0}({1}) VALUES ({2});", tableName, columns, values);
            }

            return dataProvider.Execute(sqlBuilder.ToString(), objectValues, transaction) > 0;
        }

        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="TSource"></typeparam>
        /// <param name="dataProvider"></param>
        /// <param name="source"></param>
        /// <param name="tableName"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static async Task<bool> AddAsync<TSource>(this IDataProvider dataProvider, TSource source, string tableName, IDbTransaction transaction)
        {
            var sqlBuilder = new StringBuilder();

            //var fields = GetFields(source.GetType());

            //当传入的表名是空的时候，自动使用类名转换成小写字母做为表名。
            if (string.IsNullOrWhiteSpace(tableName))
            {
                tableName = source.GetType().Name.ToLower();
            }

            IDictionary<string, object> objectValues = FindProperties<TSource>(source);
            var useFileds = objectValues.Keys;

            if (dataProvider.DatabaseType == DatabaseType.SqlServer)
            {
                var columns = string.Join(",", "[" + useFileds + "]");
                var values = string.Join(",", useFileds.Select(p => "@" + p));

                sqlBuilder.AppendFormat("INSERT INTO {0}({1}) VALUES ({2});", tableName, columns, values);
            }

            if (dataProvider.DatabaseType == DatabaseType.MySql || dataProvider.DatabaseType == DatabaseType.MariaDb)
            {
                var columns = string.Join(",", useFileds.Select(p => $"`{p}`"));
                var values = string.Join(",", useFileds.Select(p => "@" + p));

                sqlBuilder.AppendFormat("INSERT INTO {0}({1}) VALUES ({2});", tableName, columns, values);
            }

            return await dataProvider.ExecuteAsync(sqlBuilder.ToString(), objectValues, transaction) > 0;
        }

        #endregion Add

        #region Update

        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="TSource"></typeparam>
        /// <param name="dataProvider"></param>
        /// <param name="source"></param>
        /// <param name="fieldId"></param>
        /// <param name="tableName"></param>
        /// <param name="idField"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static bool Update<TSource>(this IDataProvider dataProvider, TSource source, object fieldId, string tableName, string idField, IDbTransaction transaction)
        {
            var sqlBuilder = new StringBuilder();

            //当传入的表名是空的时候，自动使用类名转换成小写字母做为表名。
            if (string.IsNullOrWhiteSpace(tableName))
            {
                tableName = source.GetType().Name.ToLower();
            }

            //如果没有设置 id 字段名，则使用默认的“id”。
            if (string.IsNullOrWhiteSpace(idField))
            {
                idField = "id";
            }

            IDictionary<string, object> objectValues = FindProperties(source);

            sqlBuilder.AppendFormat("UPDATE {0} SET ", tableName);

            int index = 0;

            objectValues.Keys.ForEach(item =>
            {
                if (idField == item)
                {
                    return;
                }

                if (index > 0)
                {
                    sqlBuilder.Append(',');
                }

                if (dataProvider.DatabaseType == DatabaseType.SqlServer)
                {
                    sqlBuilder.AppendFormat("[{0}]", item);
                }
                else
                {
                    sqlBuilder.Append($"`{item}`");
                }
                sqlBuilder.Append(" = " + dataProvider.GetParameterTag());
                sqlBuilder.Append(item);

                index++;
            });

            if (!string.IsNullOrWhiteSpace(idField) && fieldId != null)
                sqlBuilder.AppendFormat(" WHERE {0} = '{1}'", idField, fieldId);

            return dataProvider.Execute(sqlBuilder.ToString(), objectValues, transaction) > 0;
        }

        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="TSource"></typeparam>
        /// <param name="dataProvider"></param>
        /// <param name="source"></param>
        /// <param name="fieldId"></param>
        /// <param name="tableName"></param>
        /// <param name="idField"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static async Task<bool> UpdateAsync<TSource>(this IDataProvider dataProvider, TSource source, object fieldId, string tableName, string idField, IDbTransaction transaction)
        {
            var sqlBuilder = new StringBuilder();

            //当传入的表名是空的时候，自动使用类名转换成小写字母做为表名。
            if (string.IsNullOrWhiteSpace(tableName))
            {
                tableName = source.GetType().Name.ToLower();
            }

            //如果没有设置 id 字段名，则使用默认的“id”。
            if (string.IsNullOrWhiteSpace(idField))
            {
                idField = "id";
            }

            IDictionary<string, object> objectValues = FindProperties(source);

            sqlBuilder.AppendFormat("UPDATE {0} SET ", tableName);

            int index = 0;

            objectValues.Keys.ForEach(item =>
            {
                if (idField == item)
                {
                    return;
                }

                if (index > 0)
                {
                    sqlBuilder.Append(',');
                }

                if (dataProvider.DatabaseType == DatabaseType.SqlServer)
                {
                    sqlBuilder.AppendFormat("[{0}]", item);
                }
                else
                {
                    sqlBuilder.Append($"`{item}`");
                }
                sqlBuilder.Append(" = " + dataProvider.GetParameterTag());
                sqlBuilder.Append(item);

                index++;
            });

            if (!string.IsNullOrWhiteSpace(idField) && fieldId != null)
                sqlBuilder.AppendFormat(" WHERE {0} = '{1}'", idField, fieldId);

            return await dataProvider.ExecuteAsync(sqlBuilder.ToString(), objectValues, transaction) > 0;
        }

        #endregion Update

        #region private method

        /// <summary>
        /// 缓存字段版本号。
        /// </summary>
        private const string FIELD_VERSION = "20181225001";

        ///// <summary>
        ///// 获得数据实体的字段信息。
        ///// </summary>
        ///// <param name="dataProvider"></param>
        ///// <param name="type"></param>
        ///// <returns></returns>
        //private static IEnumerable<string> GetFields(this IDataProvider dataProvider, Type type)
        //{
        //    if (dataProvider != null)
        //    {
        //    }

        //    return GetFields(type);
        //}

        //private static readonly MonoConcurrentDictionary<string, IEnumerable<string>> _fields = new MonoConcurrentDictionary<string, IEnumerable<string>>();

        ///// <summary>
        ///// 获得数据实体的字段信息。
        ///// </summary>
        ///// <param name="type"></param>
        ///// <returns></returns>
        //private static IEnumerable<string> GetFields(Type type)
        //{
        //    var key = type.FullName + "_v." + FIELD_VERSION;

        //    if (!_fields.ContainsKey(key))
        //    {
        //        var fields = new List<string>();

        //        type.GetProperties().ForEach(item =>
        //        {
        //            if (!( typeof(IEnumerable).IsAssignableFrom(item.PropertyType) ) || item.PropertyType == typeof(string))
        //            {
        //                fields.Add(item.Name);
        //            }
        //        });

        //        _fields[key] = fields;
        //    }

        //    return _fields[key];
        //}

        private static MonoConcurrentDictionary<string, object> FindProperties<TSource>(TSource source)
        {
            var objectValues = new MonoConcurrentDictionary<string, object>();

            if (source is IDictionary<string, object>)
            {
                var parameterList = source as IDictionary<string, object>;

                if (parameterList.Any())
                {
                    foreach (var property in parameterList)
                    {
                        if (property.Value != null)
                        {
                            if (property.Value is DateTime)
                            {
                                var value = Convert.ToDateTime(property.Value);

                                if (value >= DateTimeDefault.Value)
                                {
                                    objectValues[property.Key] = value;
                                }
                            }
                            else
                            {
                                objectValues[property.Key] = property.Value;
                            }
                        }
                    }
                }
            }
            else
            {
                var properties = GetProperties<TSource>(source);

                if (!properties.Any())
                {
                    return null;
                }

                foreach (var property in properties)
                {
                    var propertyValue = property.GetValue(source);

                    if (propertyValue != null)
                    {
                        if (propertyValue is DateTime)
                        {
                            var value = Convert.ToDateTime(propertyValue);

                            if (value >= DateTimeDefault.Value)
                            {
                                objectValues[property.Name] = value;
                            }
                        }
                        else
                        {
                            objectValues[property.Name] = propertyValue;
                        }
                    }
                }
            }

            return objectValues;
        }

        private static readonly MonoConcurrentDictionary<string, IEnumerable<ObjectPropertyInfo>> _properties = new();

        /// <summary>
        /// 获得数据实体的字段信息。
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        private static IEnumerable<ObjectPropertyInfo> GetProperties<TSource>(TSource obj)
        {
            var key = obj.GetType().FullName + "_properties_v." + FIELD_VERSION;

            if (!_properties.ContainsKey(key))
            {
                _properties[key] = PropertyManager.Instance.FindProperties(obj);
            }

            return _properties[key];
        }

        /// <summary>
        /// 获得参数符。
        /// </summary>
        /// <param name="dataProvider">数据访问器。</param>
        /// <returns>参数符。</returns>
        private static string GetParameterTag(this IDataProvider dataProvider)
        {
            var tag = "@";

            if (dataProvider.DatabaseType == DatabaseType.SqlServer)
            {
                tag = "@";
            }

            if (dataProvider.DatabaseType == DatabaseType.MySql)
            {
                tag = "@";
            }

            return tag;
        }

        #endregion private method
    }
}